package dao;

import entity.EquipmentBean;
import entity.Page;
import entity.PersonBean;
import utils.DBUtil;
import utils.DBUtil_Plus;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PersonDAO {
    /**
     * 查询人员信息
     * @param person_number 工号
     * @param person_name 姓名
     * @param degree 最高学历
     * @param person_position 职位
     * @param telephone 电话号码
     * @return List<PersonBean> 符合查询条件的人员列表
     */
    public List<PersonBean> findPerson(String person_number, String person_name, String degree, String person_position, String telephone, Page page){
        List<PersonBean> personList = new ArrayList<PersonBean>();
        //String countSql = "select count(*) from person_list where 1 = 1";
        String selectSql = "select * from person_list where 1 = 1";
        String sql = "";

        if (!person_number.isEmpty())
            sql += " and person_number like ?";
        if (!person_name.isEmpty())
            sql += " and person_name like ?";
        if (!degree.isEmpty())
            sql += " and degree like ?";
        if (!person_position.isEmpty())
            sql += " and person_position like ?";
        if (!telephone.isEmpty())
            sql += " and telephone like ?";
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtil_Plus.getConnection();
          /*  pstmt = conn.prepareStatement(selectSql+sql);
*/
            // 查询满足条件的总记录数
            String countQuery = "SELECT CEIL(COUNT(*)/?) AS totalPages FROM (" + selectSql+" "+sql + ") AS sub";
            pstmt = conn.prepareStatement(countQuery);
            pstmt.setInt(1, page.getPageSize());

            int paramIndex = 1;
            if (!person_number.isEmpty()) {
                person_number = "%" + person_number + "%";
                paramIndex++;
                pstmt.setString(paramIndex, person_number);
                }
                if (!person_name.isEmpty()) {
                    person_name = "%" + person_name + "%";
                    paramIndex++;
                    pstmt.setString(paramIndex, person_name);
                }
                if (!degree.isEmpty()) {
                    paramIndex++;
                    pstmt.setString(paramIndex, degree);
                }
                if (!person_position.isEmpty()) {
                    person_position = "%" + person_position + "%";
                    paramIndex++;
                    pstmt.setString(paramIndex, person_position);
                }
                if (!telephone.isEmpty()) {
                    telephone = "%" + telephone + "%";
                    paramIndex++;
                    pstmt.setString(paramIndex, telephone);
                }


            rs = pstmt.executeQuery();
            // 提取总页数
            if (rs.next()) {
                page.setTotalPage(rs.getInt("totalPages"));
            }

            // 添加排序和分页
            String finalSql = selectSql+" "+sql + " ORDER BY id LIMIT " + page.getCurrentPageTotalRows() + ", " + page.getPageSize();
            pstmt = conn.prepareStatement(finalSql);

            paramIndex = 0;
            if (!person_number.isEmpty()) {
                person_number = "%" + person_number + "%";
                paramIndex++;
                pstmt.setString(paramIndex, person_number);
            }
            if (!person_name.isEmpty()) {
                person_name = "%" + person_name + "%";
                paramIndex++;
                pstmt.setString(paramIndex, person_name);
            }
            if (!degree.isEmpty()) {
                paramIndex++;
                pstmt.setString(paramIndex, degree);
            }
            if (!person_position.isEmpty()) {
                person_position = "%" + person_position + "%";
                paramIndex++;
                pstmt.setString(paramIndex, person_position);
            }
            if (!telephone.isEmpty()) {
                telephone = "%" + telephone + "%";
                paramIndex++;
                pstmt.setString(paramIndex, telephone);
            }
            // 执行查询
            rs = pstmt.executeQuery();

            // 访问数据
            while (rs.next()) {
                PersonBean obj = new PersonBean();
                obj.setId(rs.getInt("id"));
                obj.setPerson_number(rs.getString("person_number"));
                obj.setPerson_name(rs.getString("person_name"));
                obj.setDepartment(rs.getString("department"));
                obj.setDate(rs.getDate("date"));
                obj.setDegree(rs.getString("degree"));
                obj.setMajor(rs.getString("major"));
                obj.setPerson_position(rs.getString("person_position"));
                obj.setTelephone(rs.getString("telephone"));
                obj.setAddress(rs.getString("address"));
                obj.setIntroduce(rs.getString("introduce"));
                personList.add(obj);
            }

        }catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil_Plus.closeConnection(conn);
        }
        return personList;
    }

    /**
     * 删除人员信息
     * @param id 表id
     */
    public void deletePerson(String id){
        Connection conn = null;
        Statement stmt = null;
        int delete_id = Integer.parseInt(id);
        String sql = "delete from person_list where id = " + delete_id + " ";

        try {
            if (id != null && !id.trim().isEmpty()) {
                conn = DBUtil_Plus.getConnection();        // 获取执行sql的对象Statement
                stmt = conn.createStatement();
                int rs = stmt.executeUpdate(sql);
                // 创建一条数据操纵语句，如 UPDATE 或 DELETE
                String sql1 = "UPDATE person_list set id =  id - 1 where id > " + delete_id + " ";
                stmt.executeUpdate(sql1);
            }
        }catch (SQLException e) {
            e.printStackTrace(); // 可以将异常抛出或者记录到日志中
        } finally {
            // 关闭连接和语句对象
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil_Plus.closeConnection(conn);
        }
    }

    /**
     * 添加人员数据
     * @param person_number 工号
     * @param name 姓名
     * @param department 部门
     * @param time 入职时间
     * @param level 最高学历
     * @param major 专业
     * @param job 职称
     * @param telephone 手机号
     * @param address 家庭住址
     * @param introduce 个人简介
     */
    public static void addPerson(String person_number,String name,String department,String time,String level,String major,String job,String telephone,String address,String introduce){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int person_idd;
        String query;
        String sql;
        try {
            conn = DBUtil_Plus.getConnection();
            // 获取当前数据库表中数据的最后一行
            query = "SELECT id FROM person_list ORDER BY id DESC LIMIT 1";
            PreparedStatement pstmt_query = conn.prepareStatement(query);
            ResultSet rs_query = pstmt_query.executeQuery();
            if(rs_query.next()){
                person_idd = rs_query.getInt("id");
                person_idd++;
            }else{
                return;
            }

            sql = "insert into db_005.person_list(id,person_name,department,date,degree,major,person_position,telephone,address,introduce,person_number)" +
                    "values(?,?,?,?,?,?,?,?,?,?,?);";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, person_idd);
            pstmt.setString(2, name);
            pstmt.setString(3, department);
            pstmt.setDate(4, Date.valueOf(time));
            pstmt.setString(5, level);
            pstmt.setString(6, major);
            pstmt.setString(7, job);
            pstmt.setString(8, telephone);
            pstmt.setString(9, address);
            pstmt.setString(10, introduce);
            pstmt.setString(11,person_number);
            pstmt.executeUpdate();

        }catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil_Plus.closeConnection(conn);
        }
    }
    /**
     * 编辑人员数据
     * @param id 表id
     * @param person_number 工号
     * @param name 姓名
     * @param department 部门
     * @param time 入职时间
     * @param level 最高学历
     * @param major 专业
     * @param job 职称
     * @param telephone 手机号
     * @param address 家庭住址
     * @param introduce 个人简介
     */
    public static void editPerson(String id, String person_number, String name, String department, String time, String level, String major, String job, String telephone, String address, String introduce) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtil_Plus.getConnection();
            String sql = "update person_list set person_number = ?, person_name = ?, department = ?,date = ?, degree = ?, major = ?, person_position = ?, telephone = ?, address = ?, introduce = ? where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,person_number);
            pstmt.setString(2, name);
            pstmt.setString(3, department);
            pstmt.setDate(4, Date.valueOf(time));
            pstmt.setString(5, level);
            pstmt.setString(6, major);
            pstmt.setString(7, job);
            pstmt.setString(8, telephone);
            pstmt.setString(9, address);
            pstmt.setString(10, introduce);
            pstmt.setInt(11, Integer.parseInt(id));
            pstmt.executeUpdate();

        }catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil_Plus.closeConnection(conn);
        }
    }

    /**
     * 填充编辑/添加 表单数据
     * @param id id
     * @return PersonBean 编辑表单数据list
     */
    public PersonBean fillEditMessage(String id) throws SQLException {
        PersonBean editList = new PersonBean();
        if (id != null) {
            Connection conn = DBUtil_Plus.getConnection();
            // 获取执行sql的对象Statement
            Statement stmt = conn.createStatement();
            String sql = "select * from person_list where id =  " + id + " ";
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            editList.setId(rs.getInt("id"));
            editList.setPerson_number(rs.getString("person_number"));
            editList.setPerson_name(rs.getString("person_name"));
            editList.setDepartment(rs.getString("department"));
            editList.setDate(Date.valueOf(rs.getString("date")));
            editList.setDegree(rs.getString("degree"));
            editList.setMajor(rs.getString("major"));
            editList.setPerson_position(rs.getString("person_position"));
            editList.setTelephone(rs.getString("telephone"));
            editList.setAddress(rs.getString("address"));
            editList.setIntroduce(rs.getString("introduce"));
            rs.close();
            stmt.close();
            DBUtil_Plus.closeConnection(conn);
        } else {
            editList.setPerson_number("");
            editList.setPerson_name("");
            editList.setDepartment("");
            editList.setDegree("");
            editList.setMajor("");
            editList.setPerson_position("");
            editList.setTelephone("");
            editList.setAddress("");
            editList.setIntroduce("");
        }
        return editList;
    }
}

